

import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Dimension;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.KeyAdapter;
import java.awt.event.KeyEvent;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import javax.swing.DefaultComboBoxModel;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JLabel;
import javax.swing.JList;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JSplitPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.SwingConstants;
import javax.swing.table.DefaultTableModel;
import java.awt.SystemColor;
import javax.swing.UIManager;
import javax.swing.border.EtchedBorder;


public class EmpSearchPanel extends JPanel 
{
	public static Vector<MemberBean> vec = new Vector<MemberBean>();
	private JPanel panel;
	private JLabel lblNewLabel;
	private JPanel panel_1;
	private JPanel panel_2;
	private JPanel panel_3;
	private JButton btnEmpModify;
	private JButton btnEmpInput;
	private JButton btnEmpSearch;
	private JLabel lblNewLabel_13;
	private JComboBox comboBox;
	private JTextField tfFirstName;
	private JTextField tfLastName;
	private JLabel lblLastName;
	private JLabel lblFirstName;
	private JButton EmpSearchButton;
	private JScrollPane scrollPane;
	private JTable table;

	private String selCon="name";
	private String selected = "";
	
	private int selId=0;
	
	private Connection conn;
	
	private int index;
	
	DefaultTableModel model;
	
	private void connect() throws SQLException, ClassNotFoundException
	{
		conn = DBconn.getConnection();
	}
	
	private void close() throws SQLException
	{
		DBconn.close();
		conn = null;
	}
	
	/**
	 * Create the panel.
	 */
	public EmpSearchPanel() 
	{
		setBackground(new Color(173, 216, 230));
		setLayout(new BorderLayout(0, 0));
		add(getPanel(), BorderLayout.NORTH);
		add(getPanel_1(), BorderLayout.WEST);
		add(getPanel_2(), BorderLayout.CENTER);

		MemberBean mem;
		try 
		{
			connect();
			
			String sql = "select e.employee_id, e.first_name, e.last_name, e.email, "
					+ " e.phone_number, e.hire_date, e.job_id, e.manager_id, d.department_name, "
					+ " e.department_id, e.zipcode, e.address from emp e, departments d where e.department_id=d.department_id";
			
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			
			while (rs.next()) 
			{
				mem = new MemberBean();
				mem.setEmpId(rs.getInt("employee_id"));
				mem.setFirName(rs.getString("first_name"));
				mem.setLastName(rs.getString("last_name"));
				mem.setEmail(rs.getString("email"));
				mem.setpNum(rs.getString("phone_number"));
				mem.sethDate(rs.getString("hire_date"));
				mem.setJobId(rs.getString("job_id"));
				mem.setMngId(rs.getInt("manager_id"));
				mem.setDeptName(rs.getString("department_name"));
				mem.setDeptId(rs.getInt("department_id"));
				mem.setZipcode(rs.getString("zipcode"));
				mem.setAddr(rs.getString("address"));
				
				vec.add(mem);
			}
			System.out.println(vec.size());
		} 
		catch (ClassNotFoundException | SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	private JPanel getPanel() 
	{
		if (panel == null) 
		{
			panel = new JPanel();
			panel.setBackground(new Color(245, 255, 250));
			panel.setPreferredSize(new Dimension(10, 34));
			panel.setLayout(new BorderLayout(0, 0));
			panel.add(getLabel_1(), BorderLayout.WEST);
			panel.add(getPanel_3(), BorderLayout.CENTER);
		}
		return panel;
	}
	private JLabel getLabel_1() 
	{
		if (lblNewLabel == null) 
		{
			lblNewLabel = new JLabel("사원 조회"); //$NON-NLS-1$
			lblNewLabel.setBackground(new Color(189, 183, 107));
			lblNewLabel.setForeground(new Color(0, 0, 0));
			lblNewLabel.setHorizontalAlignment(SwingConstants.CENTER);
			lblNewLabel.setFont(new Font("돋움", Font.BOLD, 17));
			lblNewLabel.setPreferredSize(new Dimension(120, 15));
		}
		return lblNewLabel;
	}
	private JPanel getPanel_1() 
	{
		if (panel_1 == null) 
		{
			panel_1 = new JPanel();
			panel_1.setBackground(new Color(245, 255, 250));
			panel_1.setPreferredSize(new Dimension(120, 10));
			panel_1.setMinimumSize(new Dimension(30, 30));
			panel_1.setLayout(null);
			panel_1.add(getLblNewLabel_13());
		}
		return panel_1;
	}
	
	private JPanel getPanel_2() 
	{
		if (panel_2 == null) 
		{
			panel_2 = new JPanel();
			panel_2.setBackground(new Color(245, 255, 250));
			panel_2.setLayout(null);
			panel_2.add(getComboBox());
			panel_2.add(getTfFirstName());
			panel_2.add(getTfLastName());
			panel_2.add(getLblLastName());
			panel_2.add(getLabel_1_1());
			panel_2.add(getEmpSearchButton());
			panel_2.add(getScrollPane());
		}
		return panel_2;
	}
	
	private JPanel getPanel_3() 
	{
		if (panel_3 == null) 
		{
			panel_3 = new JPanel();
			panel_3.setBackground(new Color(245, 255, 250));
			panel_3.setLayout(new BorderLayout(0, 0));
			panel_3.add(getBtnEmpModify(), BorderLayout.EAST);
			panel_3.add(getBtnEmpInput(), BorderLayout.CENTER);
			panel_3.add(getBtnEmpSearch(), BorderLayout.WEST);
		}
		return panel_3;
	}
	
	private JButton getBtnEmpModify() 
	{
		if (btnEmpModify == null) 
		{
			btnEmpModify = new JButton(); //$NON-NLS-1$
			btnEmpModify.setForeground(new Color(255, 255, 255));
			btnEmpModify.setFont(new Font("Dialog", Font.BOLD, 17));
			btnEmpModify.setBackground(new Color(135, 206, 250));
			btnEmpModify.setPreferredSize(new Dimension(160, 9));
			btnEmpModify.setMaximumSize(new Dimension(160, 9));
			btnEmpModify.setMinimumSize(new Dimension(160, 9));
			btnEmpModify.setText("수정 & 삭제");
		}
		return btnEmpModify;
	}
	
	private JButton getBtnEmpInput() 
	{
		if (btnEmpInput == null) 
		{
			btnEmpInput = new JButton(); //$NON-NLS-1$
			btnEmpInput.setForeground(new Color(255, 255, 255));
			btnEmpInput.setFont(new Font("Dialog", Font.BOLD, 17));
			btnEmpInput.setBackground(new Color(135, 206, 250));
			btnEmpInput.setText("사원 등록");
			btnEmpInput.setMinimumSize(new Dimension(120, 23));
			btnEmpInput.setMaximumSize(new Dimension(120, 23));
			btnEmpInput.setPreferredSize(new Dimension(160, 23));
		}
		return btnEmpInput;
	}
	private JButton getBtnEmpSearch() 
	{
		if (btnEmpSearch == null) 
		{
			btnEmpSearch = new JButton(); //$NON-NLS-1$
			btnEmpSearch.setForeground(new Color(255, 255, 255));
			btnEmpSearch.setFont(new Font("Dialog", Font.BOLD, 17));
			btnEmpSearch.setBackground(new Color(135, 206, 250));
			btnEmpSearch.setText("사원 조회");
			btnEmpSearch.setPreferredSize(new Dimension(160, 23));
			btnEmpSearch.setMinimumSize(new Dimension(120, 23));
			btnEmpSearch.setMaximumSize(new Dimension(120, 23));
		}
		return btnEmpSearch;
	}
	private JLabel getLblNewLabel_13() {
		if (lblNewLabel_13 == null) {
			lblNewLabel_13 = new JLabel("조회 사원의 ");
			lblNewLabel_13.setFont(new Font("굴림", Font.BOLD, 15));
			lblNewLabel_13.setBounds(33, 13, 87, 22);
		}
		return lblNewLabel_13;
	}
	private JComboBox getComboBox() 
	{
		if (comboBox == null) 
		{
			comboBox = new JComboBox();
			comboBox.setBorder(new EtchedBorder(EtchedBorder.LOWERED, null, new Color(70, 130, 180)));
			comboBox.setBackground(UIManager.getColor("Button.light"));
			comboBox.setBounds(12, 10, 88, 27);
			comboBox.addActionListener(new ActionListener() 
			{
				public void actionPerformed(ActionEvent comBox) 
				{
					String selectModel = comboBox.getSelectedItem().toString();
					
					if (selectModel.trim().equals("ID")) 
					{
						lblFirstName.setText("");
						lblFirstName.setText("ID :");
						lblLastName.setVisible(false);
						tfLastName.setVisible(false);
						tfFirstName.setText("");
					}
					else
					{
						lblFirstName.setText("");
						lblFirstName.setText("First : ");
						lblLastName.setVisible(true);
						tfLastName.setVisible(true);
						tfFirstName.setText("");
					}
					
					JComboBox<String> jcb = (JComboBox<String>)comBox.getSource();
					
					if (jcb.getSelectedItem().equals("ID")) 
					{
						selCon = "id";
					}
					if (jcb.getSelectedItem().equals("Name")) 
					{
						selCon = "name";
					}
				}
			});
			comboBox.setModel(new DefaultComboBoxModel(new String[] {"Name", "ID"}));
		}
		return comboBox;
	}
	
	private JTextField getTfFirstName() 
	{
		if (tfFirstName == null) 
		{
			tfFirstName = new JTextField("");
			tfFirstName.setBounds(162, 10, 72, 27);
			tfFirstName.addKeyListener(new KeyAdapter() 
			{
				@Override
				public void keyPressed(KeyEvent key)
				{
					if (key.getKeyCode() == KeyEvent.VK_ENTER) 
					{
						search();
					}
				}
			});
			tfFirstName.setColumns(10);
		}
		return tfFirstName;
	}
	
	private JTextField getTfLastName() 
	{
		if (tfLastName == null) 
		{
			tfLastName = new JTextField("");
			tfLastName.setBounds(293, 10, 62, 27);
			tfLastName.addKeyListener(new KeyAdapter()
			{
				@Override
				public void keyPressed(KeyEvent key)
				{
					if (key.getKeyCode()==KeyEvent.VK_ENTER) 
					{
						search();
					}
				}
			});
			tfLastName.setColumns(10);
		}
		return tfLastName;
	}
	private JLabel getLblLastName() 
	{
		if (lblLastName == null) 
		{
			lblLastName = new JLabel("Last :");
			lblLastName.setBounds(246, 10, 55, 27);
		}
		return lblLastName;
	}
	
	private JLabel getLabel_1_1() 
	{
		if (lblFirstName == null) 
		{
			lblFirstName = new JLabel("First :");
			lblFirstName.setBounds(123, 10, 48, 27);
		}
		return lblFirstName;
	}
	
	private JButton getEmpSearchButton() 
	{
		if (EmpSearchButton == null) 
		{
			EmpSearchButton = new JButton("Search");
			EmpSearchButton.setBorder(new EtchedBorder(EtchedBorder.LOWERED, null, new Color(70, 130, 180)));
			EmpSearchButton.setBackground(UIManager.getColor("Button.light"));
			EmpSearchButton.setBounds(397, 10, 79, 27);
			EmpSearchButton.addActionListener(new ActionListener() 
			{
				public void actionPerformed(ActionEvent arg0) 
				{
					search();
				}
			});
		}
		return EmpSearchButton;
	}
	
	private JScrollPane getScrollPane() 
	{
		if (scrollPane == null) 
		{
			scrollPane = new JScrollPane();
			scrollPane.setBackground(new Color(255, 255, 255));
			scrollPane.setBounds(12, 46, 482, 355);
			scrollPane.setViewportView(getTable());
		}
		return scrollPane;
	}
	
	private JTable getTable() 
	{
		if (table == null)
		{
			table = new JTable();
			table.addMouseListener(new MouseAdapter() 
			{
				@Override
				public void mouseClicked(MouseEvent arg0) 
				{
					Object value = table.getValueAt(table.getSelectedRow(), 0);
					
					int selId = (Integer)value;
					
					System.out.println("v mouse : " + selId);

					search();
				}
			});
		}
		return table;
	}
	
	
	void search()
	{
		try 
		{
			model = new DefaultTableModel();
			model.setColumnIdentifiers(new String[]{"ID", "First Name", "Last Name","Department Name", "Email", "Phone Number", "Manager ID"});
	
					

			connect();
			
			String sql = "select e.employee_id, e.first_name, e.last_name, d.department_name, e.department_id, e.email, e.phone_number, e.manager_id from emp e, departments d where e.department_id=d.department_id and ";
			
			if (selCon.equals("id")) 
			{
				sql+= " employee_id = ?";
			}
			if (selCon.equals("name")) 
			{
				if (tfFirstName.getText().trim().equals("")&&!tfLastName.getText().trim().equals("")) 
				{
					sql+=" last_name like ? ";
					selected = "onlyLn";//pstmt.setString(1, "%"+ln+"%");
				}
				
				if (tfFirstName.getText().trim().equals("")&&tfLastName.getText().trim().equals("")) 
				{
					JOptionPane.showMessageDialog(null, "검색할 이름을 입력해주십시오", "Error", JOptionPane.ERROR_MESSAGE);
					return;
				}

				if (!tfFirstName.getText().trim().equals("")&&tfLastName.getText().trim().equals("")) 
				{
					sql+=" first_name like ? ";
					selected = "onlyFn";
				}

				if (!tfFirstName.getText().trim().equals("")&&!tfLastName.getText().trim().equals("")) 
				{
					sql+=" first_name like ? and last_name like ? ";
					selected = "Fnln";
				}

			}
			
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			if (selCon.equals("id")) 
			{
				pstmt.setInt(1, Integer.parseInt(tfFirstName.getText()));
			}
			else if(selected.equals("onlyFn"))
			{
				pstmt.setString(1, "%"+tfFirstName.getText()+"%");
			}
			else if(selected.equals("onlyLn"))
			{
				pstmt.setString(1,"%"+tfLastName.getText()+"%" );
			}
			else if (selected.equals("Fnln")) 
			{
				pstmt.setString(1, "%"+tfFirstName.getText()+"%");
				pstmt.setString(2, "%"+tfLastName.getText()+"%" );
			}

			ResultSet rs = pstmt.executeQuery();

			Vector<MemberBean> v = new Vector<MemberBean>();
			
			MemberBean bean;
			
			while (rs.next()) 
			{
				bean = new MemberBean();
				bean.setEmpId(rs.getInt("employee_id"));
				bean.setFirName(rs.getString("first_name"));
				bean.setLastName(rs.getString("last_name"));
				bean.setDeptId(rs.getInt("department_id"));
				bean.setDeptName(rs.getString("department_name"));
				bean.setEmail(rs.getString("email"));
				bean.setpNum(rs.getString("phone_number"));
				bean.setMngId(rs.getInt("manager_id"));
				v.add(bean);
			}
		
			int size = v.size();
			
			
			for (MemberBean m : v) 
			{
				model.addRow(new Object[]{m.getEmpId(), m.getFirName(), m.getLastName(), m.getDeptName(), m.getEmail(), m.getpNum(), m.getMngId()});
			}
			
			System.out.println(v.size());
			
		
		
		
			
			rs.close();
			pstmt.close();
			close();
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		//============================================================================

		table.setModel(model);
	}
}
